package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageBox;
import com.moleq.models.BottleSetting;
import com.moleq.models.GlobalInfo;

public class BottleSettingService {
	private DatabaseHelper DBHelper;
	private String errorString;
	private Context mContext;

	public BottleSettingService() {

	}

	public BottleSettingService(Context context) {
//		this.DBHelper = new DatabaseHelper(context);
		mContext = context;
		this.DBHelper = new DatabaseHelper(GlobalInfo.getInstance().ShareContext);
	}
	
	public List<BottleSetting> FindAll(){
		List<BottleSetting> obj = new ArrayList<BottleSetting>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME,null);;
//		Cursor cursor = DBHelper.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME,null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		while(cursor.moveToNext()){
			int bo_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_NO));
			String bo_desc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_DESC));
			double bo_amt  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_AMT));
			String create_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.CREATE_TIME));
			String update_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.UPDATE_TIME));
			boolean bo_envf_flag = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FLAG))== 1?true:false;
			double bo_envf_amt = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_AMT));
			boolean bo_envf_tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX1))== 1?true:false;
			boolean bo_envf_tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX2))== 1?true:false;
			boolean bo_envf_tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX3))== 1?true:false;
			boolean bo_envf_tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX4))== 1?true:false;
			boolean bo_envf_tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX5))== 1?true:false;
			boolean bo_envf_tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX6))== 1?true:false;
			boolean bo_envf_fs = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FS))== 1?true:false;
			boolean bo_envf_wic = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_WIC))== 1?true:false;
			
			BottleSetting bs = new BottleSetting(bo_no,bo_desc,bo_amt,bo_envf_flag,bo_envf_amt,bo_envf_tax1
					,bo_envf_tax2,bo_envf_tax3,bo_envf_tax4,bo_envf_tax5,bo_envf_tax6,bo_envf_fs,bo_envf_wic,create_datetime,update_datetime);
			bs.setType("O");
			
			obj.add(bs);
		}
		cursor.close();
		return obj;
	}
	
	public BottleSetting Find(int bo){
		
		BottleSetting obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" where bo_no = "+bo,null);
//		Cursor cursor = DBHelper.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" where bo_no = "+bo,null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return null;
		}
		while(cursor.moveToNext()){
			int bo_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_NO));
			String bo_desc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_DESC));
			double bo_amt  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_AMT));
			String create_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.CREATE_TIME));
			String update_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.UPDATE_TIME));
			boolean bo_envf_flag = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FLAG))== 1?true:false;
			double bo_envf_amt = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_AMT));
			boolean bo_envf_tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX1))== 1?true:false;
			boolean bo_envf_tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX2))== 1?true:false;
			boolean bo_envf_tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX3))== 1?true:false;
			boolean bo_envf_tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX4))== 1?true:false;
			boolean bo_envf_tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX5))== 1?true:false;
			boolean bo_envf_tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX6))== 1?true:false;
			boolean bo_envf_fs = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FS))== 1?true:false;
			boolean bo_envf_wic = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_WIC))== 1?true:false;

			
			obj = new BottleSetting(bo_no,bo_desc,bo_amt,bo_envf_flag,bo_envf_amt,bo_envf_tax1
					,bo_envf_tax2,bo_envf_tax3,bo_envf_tax4,bo_envf_tax5,bo_envf_tax6,bo_envf_fs,bo_envf_wic,create_datetime,update_datetime);
					
		}
		cursor.close();
		return obj;	
	}
	
public BottleSetting Find(String name){
		
		BottleSetting obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" where bo_desc = '" + name + "'",null);
//		Cursor cursor = DBHelper.rawQuery("select * from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" where bo_no = "+bo,null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return null;
		}
		while(cursor.moveToNext()){
			int bo_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_NO));
			String bo_desc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_DESC));
			double bo_amt  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_AMT));
			String create_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.CREATE_TIME));
			String update_datetime  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.BottleSetting.UPDATE_TIME));
			boolean bo_envf_flag = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FLAG))== 1?true:false;
			double bo_envf_amt = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_AMT));
			boolean bo_envf_tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX1))== 1?true:false;
			boolean bo_envf_tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX2))== 1?true:false;
			boolean bo_envf_tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX3))== 1?true:false;
			boolean bo_envf_tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX4))== 1?true:false;
			boolean bo_envf_tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX5))== 1?true:false;
			boolean bo_envf_tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_TAX6))== 1?true:false;
			boolean bo_envf_fs = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_FS))== 1?true:false;
			boolean bo_envf_wic = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.BottleSetting.BO_ENVF_WIC))== 1?true:false;

			
			obj = new BottleSetting(bo_no,bo_desc,bo_amt,bo_envf_flag,bo_envf_amt,bo_envf_tax1
					,bo_envf_tax2,bo_envf_tax3,bo_envf_tax4,bo_envf_tax5,bo_envf_tax6,bo_envf_fs,bo_envf_wic,create_datetime,update_datetime);
					
		}
		cursor.close();
		return obj;	
	}
	
	public boolean ItemHaveBottle(int bottleNo){
		boolean result = false;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select F01 from t_item_setting where F158 = '" + bottleNo + "'", null);
//		Cursor cursor = DBHelper.rawQuery("select F01 from t_item_setting where F158 = '" + bottleNo + "'", null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return result;
		}
		if (cursor.moveToNext()) {
			result = true;
		}
		cursor.close();
		return result;
	}
	
	public boolean updateSetBottleNo(int bottle_no){
		boolean check = true;
		errorString = DBHelper.execSQL("UPDATE "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" set bo_no = bo_no -1 where bo_no > "+ bottle_no);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean insertBottleSetting(BottleSetting obj){
		boolean check = true;
		String sql,desc,flag,tax1,tax2,tax3,tax4,tax5,tax6,fs,wic;
		double amt,envf_amt;
		desc = obj.getBo_desc();
		amt = obj.getBo_amt();
		flag = obj.isBo_envf_flag()?"1":"0";
		envf_amt = obj.getBo_envf_amt();
		tax1 = obj.isBo_envf_tax1()?"1":"0";
		tax2 = obj.isBo_envf_tax2()?"1":"0";
		tax3 = obj.isBo_envf_tax3()?"1":"0";
		tax4 = obj.isBo_envf_tax4()?"1":"0";
		tax5 = obj.isBo_envf_tax5()?"1":"0";
		tax6 = obj.isBo_envf_tax6()?"1":"0";
		fs = obj.isBo_envf_fs()?"1":"0";
		wic = obj.isBo_envf_wic()?"1":"0";
		
		sql = "INSERT INTO "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" values";
		sql += "(null,'"+ desc +"',"+ amt +",strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),'" +
				flag +"',"+ envf_amt +",'"+ tax1 +"','"+ tax2 +"','"+ tax3 +"','"+ tax4 +"','"+ tax5 +"','" +
				tax6 +"','"+ fs +"','"+ wic +"')";
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean updateBottleSetting(BottleSetting obj){
		boolean check = true;
		String sql,desc,flag,tax1,tax2,tax3,tax4,tax5,tax6,fs,wic;
		double amt,envf_amt;
		int no = obj.getBo_no();
		desc = obj.getBo_desc();
		amt = obj.getBo_amt();
		flag = obj.isBo_envf_flag()?"1":"0";
		envf_amt = obj.getBo_envf_amt();
		tax1 = obj.isBo_envf_tax1()?"1":"0";
		tax2 = obj.isBo_envf_tax2()?"1":"0";
		tax3 = obj.isBo_envf_tax3()?"1":"0";
		tax4 = obj.isBo_envf_tax4()?"1":"0";
		tax5 = obj.isBo_envf_tax5()?"1":"0";
		tax6 = obj.isBo_envf_tax6()?"1":"0";
		fs = obj.isBo_envf_fs()?"1":"0";
		wic = obj.isBo_envf_wic()?"1":"0";
		
		sql = "UPDATE "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" set ";
		sql += "bo_desc = '"+ desc +"',bo_amt = "+ amt +",update_datetime = strftime('%Y%m%d%H%M%S','now')," +
				"bo_envf_flag = '"+ flag +"',bo_envf_amt = "+ envf_amt +",bo_envf_tax1 = '"+ tax1 +"'," +
				"bo_envf_tax2 = '"+ tax2 +"',bo_envf_tax3 = '"+ tax3 +"',bo_envf_tax4 = '"+ tax4+"'," +
				"bo_envf_tax5 = '"+ tax5 +"',bo_envf_tax6 = '"+ tax6 +"',bo_envf_fs = '"+ fs +"'," +
				"bo_envf_wic = '"+ wic +"' where bo_no = " + no;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean deleteBottleSetting(int no){
		boolean check = true;
		String sql = "delete from "+ DatabaseSchema.BottleSetting.SCHEMA_NAME +" where bo_no = "+ no;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public int getMaxBottleNo(){
		int order = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select Max(bo_no) from " + DatabaseSchema.BottleSetting.SCHEMA_NAME, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return order;
		}
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
		}
		cursor.close();
		return order;
	}
}
